﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

/// <summary>
/// Summary description for SQLHelper
/// </summary>
public class SQLHelper
{
    private SqlCommand cmd;
    private bool isOpen;
    private string commandText = "";
    public string CommandText
    {
        set { commandText = value; }
        get { return commandText; }
    }
    private CommandType commandType = CommandType.Text;
    public CommandType CommandType
    {
        set { commandType = value; }
        get { return commandType; }
    }
    private string returnValueParameter = "";
    public string ReturnValueParameter
    {
        set { returnValueParameter = value; }
        get { return returnValueParameter; }
    }
    private string[] parameterCollection = null;
    public string[] ParameterCollection
    {
        set { parameterCollection = value; }
        get { return parameterCollection; }
    }
    private string[] valueCollection;
    public string[] ValueCollection
    {
        set { valueCollection = value; }
        get { return valueCollection; }
    }
    private int returnValue=0;
    public int ReturnValue
    {
        get { return returnValue; }
    }
    public SQLHelper()
    {
        string strSqlConnection = ConfigurationManager.ConnectionStrings["PhoAmThucConnectionString"].ConnectionString;
        SqlConnection conn = new SqlConnection(strSqlConnection);
        this.cmd = conn.CreateCommand();
    }
    public void Open()
    {
        if (isOpen)
        {
            return;
        }
        if (this.InTransaction)
        {
            return;
        }
        if (cmd.Connection.State != ConnectionState.Open)
        {
            cmd.Connection.Open();
            isOpen = true;
        }

    }
    //Đóng kết nối
    public void Close()
    {
        cmd.Connection.Close();
        isOpen = false;
    }
    //Giải phóng tài nguyên
    public void Dispose()
    {
        cmd.Connection.Dispose();
        cmd.Dispose();
        this.Close();
    }
    private void AddParameters()
    {
        for (int i = 0;
        i < parameterCollection.Length; i++)
        {
            cmd.Parameters.AddWithValue(
            parameterCollection[i], valueCollection[i]);
        }
    }
    private void InitParameters()
    {
        cmd.CommandText = commandText;
        cmd.CommandType = commandType;
        if (ParameterCollection != null && ParameterCollection.Length > 0)
            AddParameters();

        if (returnValueParameter != "")
        {
            SqlParameter sqlParameter = new SqlParameter();
            sqlParameter.ParameterName = returnValueParameter;
            sqlParameter.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(sqlParameter);
        }
    }
    public int ExecuteNonQuery()
    {
        InitParameters();
        int rows = cmd.ExecuteNonQuery();

        if (returnValueParameter != "")
            returnValue = Convert.ToInt32(
            cmd.Parameters[
            returnValueParameter].Value);
        cmd.Dispose();
        return rows;

    }
    public object ExecuteScalar()
    {
        InitParameters();
        object obj = cmd.ExecuteScalar();

        if (returnValueParameter != "")
            returnValue = Convert.ToInt32(
            cmd.Parameters[
            returnValueParameter].Value);

        cmd.Dispose();
        return obj;
    }
    public SqlDataReader ExcuteReader()
    {
        InitParameters();
        SqlDataReader Dr = cmd.ExecuteReader();
        cmd.Dispose();
        return Dr;
    }
    public DataSet GetDataSet()
    {
        InitParameters();
        DataSet dataSet = new DataSet();

        SqlDataAdapter sqlDataAdapter =
        new SqlDataAdapter(cmd);

        sqlDataAdapter.Fill(dataSet);
        cmd.Dispose();
        sqlDataAdapter.Dispose();
        return dataSet;
    }
    public DataTable GetDataTable()
    {
        InitParameters();
        DataTable dataTable = new DataTable();

        SqlDataAdapter sqlDataAdapter =
        new SqlDataAdapter(cmd);

        sqlDataAdapter.Fill(dataTable);
        cmd.Dispose();
        sqlDataAdapter.Dispose();
        return dataTable;
    }
    public DataTable GetDataTable(int startRecord, int maxRecord)
    {
        InitParameters();
        DataTable dataTable = new DataTable();

        SqlDataAdapter sqlDataAdapter =
        new SqlDataAdapter(cmd);
        sqlDataAdapter.Fill(startRecord, maxRecord, dataTable);
        return dataTable;
    }
    public DataRow GetDataRow()
    {
        InitParameters();
        DataRow dataRow = null;
        DataTable dataTable = new DataTable();

        SqlDataAdapter sqlDataAdapter =
        new SqlDataAdapter(cmd);
        sqlDataAdapter.Fill(dataTable);
        if (dataTable.Rows.Count > 0)
            dataRow = dataTable.Rows[0];
        cmd.Dispose();
        sqlDataAdapter.Dispose();
        return dataRow;
    }
    private bool inTran = false;
    protected bool InTransaction
    {
        set { inTran = value; }
        get { return inTran; }
    }
}
